package org.stwy.report;

import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.stwy.lease.contract.ContractAssetEntity;
import org.stwy.lease.contract.ContractEntity;
import org.stwy.lease.rent.RentEntity;
import org.stwy.pro.asset.AssetEntity;

import com.rap.common.persistence.DbUtilsDAO;
import com.rap.common.utils.DateUtil;

@Component
public class StwyReportDAO {
	@PersistenceContext
	EntityManager em;
	@Autowired
	DbUtilsDAO dbutils;
	
	public List testFind(){
		StringBuffer jpql = new StringBuffer();
		jpql.append(" select r from RoleEntity r INNER JOIN r.rolegroup where r.roleName like '%对标%' ");
		
		Query query=em.createQuery(jpql.toString());
		List list=query.getResultList();
		return list;
	}
	public List findAssetsByGroup(Date date){
		StringBuffer jpql = new StringBuffer();
		jpql.append(" select a from AssetEntity a where a.beginTs= (");
		jpql.append(" select max(b.beginTs) from AssetEntity b where b.theCode = a.theCode and b.beginTs<=:date");
		jpql.append(" )  ");
		jpql.append(" and a.beginTs<=:date ");
		jpql.append(" and a.valid = 1");
		
		Query query=em.createQuery(jpql.toString());
		query.setParameter("date", date);
		List list=query.getResultList();
		return list;
	}
	public List<RentEntity> findRentByDate(Date firstDate, Date lastDate) {
		StringBuffer jpql = new StringBuffer();
		jpql.append(" select r from RentEntity r where r.payDate>=:firstDate and r.payDate<=:lastDate and r.hasPay=1 ");
		
		Query query=em.createQuery(jpql.toString());
		query.setParameter("firstDate", firstDate);
		query.setParameter("lastDate", lastDate);
		List list=query.getResultList();
		return list;
	}
	/**
	 * 获取某日期状态为assetState的资产
	 * @param reportDate
	 * @return
	 */
	public List<AssetEntity> findAssets(Date reportDate,String assetState) {
		StringBuffer jpql = new StringBuffer();
		jpql.append(" select a from AssetEntity a where a.beginTs= (");
		jpql.append(" select max(b.beginTs) from AssetEntity b where b.theCode = a.theCode and b.beginTs<=:date");
		jpql.append(" )  ");
		jpql.append(" and a.beginTs<=:date ");
		jpql.append(" and a.valid = 1");
		jpql.append(" and a.assetState = :assetState order by a.groupOne asc,a.groupTwo");
		Query query=em.createQuery(jpql.toString());
		query.setParameter("date", reportDate);
		query.setParameter("assetState", assetState);
		List list=query.getResultList();
		return list;
	}
	public List<ContractAssetEntity> find3MonthsEndContracts(Date reportDate) {
		StringBuffer jpql = new StringBuffer();
		jpql.append(" select a from ContractAssetEntity a where a.contract.endDate<=:date and a.contract.contractState=1");
		Query query=em.createQuery(jpql.toString());
		query.setParameter("date", DateUtil.addMonth(reportDate, 3));
		List list=query.getResultList();
		return list;
	}
	public List<ContractEntity> findContracts(Date reportDate) {
		StringBuffer jpql = new StringBuffer();
		jpql.append(" select a from ContractEntity a where a.beginTs= (");
		jpql.append(" select max(b.beginTs) from ContractEntity b where b.theCode = a.theCode and b.beginTs<=:date");
		jpql.append(" )  ");
		jpql.append(" and a.beginTs<=:date ");
		jpql.append(" and a.valid = 1");
		Query query=em.createQuery(jpql.toString());
		query.setParameter("date", reportDate);
		List list=query.getResultList();
		return list;
	}
	public List<AssetEntity> findAllAssets(Date reportDate) {
		StringBuffer jpql = new StringBuffer();
		jpql.append(" select a from AssetEntity a where a.beginTs= (");
		jpql.append(" select max(b.beginTs) from AssetEntity b where b.theCode = a.theCode and b.beginTs<=:date");
		jpql.append(" )  ");
		jpql.append(" and a.beginTs<=:date ");
		jpql.append(" and a.valid = 1");
		Query query=em.createQuery(jpql.toString());
		query.setParameter("date", reportDate);
		List list=query.getResultList();
		return list;
	}
	public List<ContractAssetEntity> findBillsByMonth(String dateStr) {
		StringBuffer jpql = new StringBuffer();
		jpql.append(" select a from BillsEntity a where ");
		jpql.append(" and a.valid = 1");
		Query query=em.createQuery(jpql.toString());
		List list=query.getResultList();
		return list;
	}
}
